/*
 * @(#)ListWorkflowMonitoringAction.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.workflow.web;

import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.sax.SAXSource;
import javax.xml.transform.stream.StreamResult;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.quartz.JobDataMap;
import org.xml.sax.InputSource;

import com.dcivision.form.bean.FormRecord;
import com.dcivision.form.dao.FormRecordDAObject;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.MessageResourcesFactory;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemFunctionConstant;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.web.AbstractListAction;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.user.dao.UserRecordDAObject;
import com.dcivision.workflow.WorkflowErrorConstant;
import com.dcivision.workflow.bean.MtmWorkflowProgressSystemObject;
import com.dcivision.workflow.bean.MtmWorkflowProgressUserRecord;
import com.dcivision.workflow.bean.MtmWorkflowStepUserActor;
import com.dcivision.workflow.bean.MtmWorkflowTrackInterruptReq;
import com.dcivision.workflow.bean.WorkflowGroup;
import com.dcivision.workflow.bean.WorkflowProgress;
import com.dcivision.workflow.bean.WorkflowQueryRecord;
import com.dcivision.workflow.bean.WorkflowQueryRecordParser;
import com.dcivision.workflow.bean.WorkflowRecord;
import com.dcivision.workflow.bean.WorkflowStep;
import com.dcivision.workflow.core.ParseWorkflowQueryRecordXML;
import com.dcivision.workflow.dao.MtmWorkflowProgressSystemObjectDAObject;
import com.dcivision.workflow.dao.MtmWorkflowProgressUserRecordDAObject;
import com.dcivision.workflow.dao.WorkflowGroupDAObject;
import com.dcivision.workflow.dao.WorkflowProgressDAObject;
import com.dcivision.workflow.dao.WorkflowQueryRecordDAObject;
import com.dcivision.workflow.dao.WorkflowRecordDAObject;
import com.dcivision.workflow.dao.WorkflowStepDAObject;

/**
 * <p>Class Name:       ListWorkflowMonitoringAction.java    </p>
 * <p>Description:      The list action class for ListWorkflowMonitoring.jsp</p>
 * @author           Angus Shiu
 * @company          DCIVision Limited
 * @creation date    11/12/2003
 * @version          $Revision: 1.34.2.4 $
 */

public class ListWorkflowMonitoringAction extends AbstractListAction {

  public static final String REVISION = "$Revision: 1.34.2.4 $";

  private final String GENERATE_EXCEL = "G";
  private final String SAVE_QUERY_RECORD = "S";
  private final String EDIT_QUERY_RECORD = "EQ";
 
  protected final static String MESSAGE_RECORD_INSERT = "common.message.record_inserted";

  /** Creates a new instance of ListWorkflowMonitoringAction */
  public ListWorkflowMonitoringAction() {
    super();
    this.setListName("workflowMonitoringList");
    this.setListFunctionName("getListMonitoring");
  }

  public String getFunctionCode() {
    return(SystemFunctionConstant.WORKFLOW_MONITOR);
  }

  public String getMajorDAOClassName() {
    return("com.dcivision.workflow.dao.WorkflowProgressDAObject");
  }

  /**
   * setPageTitle
   * set the extend page title and page path.
   * default page path/title will be created by navmode and functionCode
   */
  public void setPageTitle(HttpServletRequest request, HttpServletResponse response, ActionForm form, ActionMapping mapping, ActionForward actionForward){
    if("LQ".equals(actionForward.getName())){
    String extendTitle = MessageResourcesFactory.getMessage(this.getSessionContainer(request).getSessionLocale(), "dms.label.load_query");
    request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE, extendTitle);
    request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE_SHOW_ONLY, new Boolean(true)); 
    return;
    }
    ListWorkflowMonitoringForm listForm = (ListWorkflowMonitoringForm) form;
    String queryName = listForm.getQueryName();
    if (GlobalConstant.NAV_MODE_EDIT.equals(actionForward.getName())
         || "EQ".equals(actionForward.getName())) {
      String extendTitle = MessageResourcesFactory.getMessage(this
          .getSessionContainer(request).getSessionLocale(),
              "workflow.label.edit_query_template");
      if (!Utility.isEmpty(queryName)) {
         extendTitle = extendTitle + " [" + queryName + "]";
      }
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE, extendTitle);
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE_SHOW_ONLY, new Boolean(true));
      return;
    } else if (GlobalConstant.NAV_MODE_NEW.equals(actionForward.getName())) {
      String extendTitle = MessageResourcesFactory.getMessage(this
          .getSessionContainer(request).getSessionLocale(),
              "workflow.label.new_query_template");
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE, extendTitle);
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE_SHOW_ONLY, new Boolean(true));
      return;
    } else if(GlobalConstant.NAV_MODE_LIST.equals(actionForward.getName())) {
      String extendTitle = MessageResourcesFactory.getMessage(this
          .getSessionContainer(request).getSessionLocale(),
      "workflow.label.query_result");
      if (!Utility.isEmpty(queryName)) {
        extendTitle = extendTitle + " [" + queryName + "]";
      }
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE, extendTitle);
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE_SHOW_ONLY, new Boolean(true));
      return;
    }
  }
  
  public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws ServletException {
    String workflowGroupID = null;
    request.setAttribute(GlobalConstant.FUNCTION_CODE_KEY, this.getFunctionCode());
    
    List wfRecordList = new ArrayList();
    WorkflowRecordDAObject wfRecordDAO = new WorkflowRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
    WorkflowProgressDAObject wfProgressDAO = new WorkflowProgressDAObject(this.getSessionContainer(request),this.getConnection(request));
    WorkflowQueryRecordDAObject workflowQueryRecordDAO = new WorkflowQueryRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
    WorkflowQueryRecordDAObject wfQueryRecordDAO = new WorkflowQueryRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
    ListWorkflowMonitoringForm listForm = (ListWorkflowMonitoringForm)form;
    MaintWorkflowQueryRecordForm  sreachForm = new MaintWorkflowQueryRecordForm();
    sreachForm.setUserRecordID(this.getSessionContainer(request).getUserRecordID().toString());
    String opMode = listForm.getOpMode();
    String generateExcel = request.getParameter("generateExcel");
    String generateAllExcel = request.getParameter("generateAllExcel");

    if(!Utility.isEmpty(generateExcel) && GENERATE_EXCEL.equals(generateExcel)){
      List dataList = new ArrayList();
      //String filePath = SystemParameterFactory.getSystemParameter(SystemParameterConstant.WORKFLOW_ATTACHMENT_PHYSICAL_PATH);
      String fileName = "WorkflowMonitoring.xls";
      if (!Utility.isEmpty(listForm.getQueryID())) {
        WorkflowQueryRecord workflowQueryRecord = this.getQueryRecordByUserRecordID(this.getSessionContainer(request).getUserRecordID(),request);
        StringBuffer queryRecordBuffer = (StringBuffer)workflowQueryRecord.getXmlQuery();
        if(!Utility.isEmpty(queryRecordBuffer) && !"".equals(queryRecordBuffer.toString())){
          ParseWorkflowQueryRecordXML parseWorkflowQueryRecordXML = new ParseWorkflowQueryRecordXML();
          parseWorkflowQueryRecordXML.getParseing(form,request,workflowQueryRecord);
        } else {
          //do null
        }
      }
      Locale locale = this.getSessionContainer(request).getSessionLocale();
      AbstractSearchForm searchForm = (AbstractSearchForm)form;
      if(GlobalConstant.TRUE.equals(generateAllExcel)){
        searchForm.setPageOffset("1000000");
      } else {
        if(!Utility.isEmpty(request.getParameter("excelPageOffset"))){
          searchForm.setCurStartRowNo(request.getParameter("excelPageOffset"));
        }
      }
      dataList = wfProgressDAO.getListMonitoring(searchForm);

      generateExcelFile(dataList,fileName,locale,request,response);

      return null;
      //  return mapping.findForward(GENERATE_EXCEL);
    }
    //if generateExcel equals "S",execute Save Current Query Record!
    if(!Utility.isEmpty(generateExcel) && this.SAVE_QUERY_RECORD.equals(generateExcel)){
      try {
        this.insertRecord(mapping,form,request,response);
        this.commit(request);
        //form.reset(mapping, request);
        addMessage(request, MESSAGE_RECORD_INSERT);
        listForm.setNavMode(GlobalConstant.NAV_MODE_VIEW);
        return mapping.findForward(GlobalConstant.NAV_MODE_VIEW);
      } catch (ApplicationException appEx) {
        this.rollback(request);
        handleApplicationException(request, appEx);
      }
      if (isError(request)) {
        return mapping.findForward(GlobalConstant.NAV_MODE_EDIT);
      }
    }
   if(!Utility.isEmpty(generateExcel) && GlobalConstant.NAV_MODE_EDIT.equals(generateExcel)){
     listForm.setNavMode(GlobalConstant.NAV_MODE_EDIT);
     return mapping.findForward(GlobalConstant.NAV_MODE_EDIT);
   }

    if(!Utility.isEmpty(request.getParameter("workflowGroupID")) && !"-".equals(request.getParameter("workflowGroupID")) ){
      workflowGroupID = request.getParameter("workflowGroupID");
      wfRecordList = wfRecordDAO.getListByWorkflowGroupIDWorkflowCategoryIDWorkflowRecordStatus(new Integer(workflowGroupID), null, GlobalConstant.RECORD_RELEASED);
    }

    String[][] wfRecordArr = new String[wfRecordList.size()][2];
    for(int i = 0; i < wfRecordList.size(); i++) {
      WorkflowRecord wfRecord = (WorkflowRecord)wfRecordList.get(i);
      wfRecordArr[i][0] = wfRecord.getID().toString();
      if (Utility.isEmpty(wfRecord.getDescription())) {
        wfRecordArr[i][1] = wfRecord.getWorkflowCode() + " [" + wfRecord.getWorkflowVersion() + "]";
      } else {
        if(wfRecord.getDescription().length() > 16){
        	wfRecordArr[i][1] = wfRecord.getWorkflowCode() + " - " + wfRecord.getDescription().substring(0,16) + "... [" + wfRecord.getWorkflowVersion() + "]";
        } else {
        	wfRecordArr[i][1] = wfRecord.getWorkflowCode() + " - " + wfRecord.getDescription() + " [" + wfRecord.getWorkflowVersion() + "]";
        }
      }
    }
    
    request.setAttribute("workflowRecordArray", wfRecordArr );
    
    //get the workflow group string array
    request.setAttribute("workflowGroupArray", this.getWorkflowGroup(request));
    
    if (!Utility.isEmpty(listForm.getNavMode()) && GlobalConstant.NAV_MODE_NEW.equals(listForm.getNavMode()) && Utility.isEmpty(listForm.getOpMode())) {
      return mapping.findForward(GlobalConstant.NAV_MODE_NEW);
    }
    if (!Utility.isEmpty(listForm.getNavMode()) && GlobalConstant.NAV_MODE_LIST.equals(listForm.getNavMode()) && GlobalConstant.OP_MODE_LIST_SEARCH_RESET.equals(listForm.getOpMode())) {
      return mapping.findForward(GlobalConstant.OP_MODE_LIST_SEARCH_RESET);
    }
    if ((!Utility.isEmpty(listForm.getNavMode()) && GlobalConstant.NAV_MODE_EDIT.equals(listForm.getNavMode())) || "QUERY.OPTION".equals(listForm.getOpMode())) {
      WorkflowQueryRecord workflowQueryRecord = this.getQueryRecordByUserRecordID(this.getSessionContainer(request).getUserRecordID(),request);
      StringBuffer queryRecordBuffer = (StringBuffer)workflowQueryRecord.getXmlQuery();
      if(!Utility.isEmpty(queryRecordBuffer) && !"".equals(queryRecordBuffer.toString())){
        ParseWorkflowQueryRecordXML parseWorkflowQueryRecordXML = new ParseWorkflowQueryRecordXML();
        parseWorkflowQueryRecordXML.getParseing(form,request,workflowQueryRecord);
      } else {
      	
        //do null
      }
      return mapping.findForward(this.EDIT_QUERY_RECORD);
    }
    
    List wfQueryRecordList = null;
    if (!Utility.isEmpty(listForm.getWorkflowGroupID())) {
      try {
        wfQueryRecordList = wfQueryRecordDAO.getListByWorkflowGroupID(new Integer(listForm.getWorkflowGroupID()) );
      }
      catch (NumberFormatException e) {
       log.error(e.toString(), e);
       wfQueryRecordList = wfQueryRecordDAO.getAllAccessibleList();
      }
    } else {
      wfQueryRecordList = wfQueryRecordDAO.getAllAccessibleList();
    }
    request.setAttribute("wfQueryRecordList", wfQueryRecordList);
    return super.execute(mapping,form,request,response);
  }


  public void getListData(ActionMapping mapping, AbstractSearchForm form, HttpServletRequest request, HttpServletResponse response) throws ApplicationException {
    SessionContainer sessionContainer = this.getSessionContainer(request);
    Connection conn = this.getConnection(request);
    ListWorkflowMonitoringForm workflowSearchForm = (ListWorkflowMonitoringForm) form;
    StringBuffer queryRecordBuffer = new StringBuffer();
    WorkflowQueryRecord workflowQueryRecord = new WorkflowQueryRecord();
    String generateAllExcel = request.getParameter("generateAllExcel");
    if(!GENERATE_EXCEL.equals(generateAllExcel)){
      workflowQueryRecord = this.getQueryRecordByUserRecordID(this.getSessionContainer(request).getUserRecordID(),request);
      queryRecordBuffer = (StringBuffer)workflowQueryRecord.getXmlQuery();
    }
    //parse xml_query to ListWrokflowMonitoringForm
    if(!Utility.isEmpty(queryRecordBuffer) && !"".equals(queryRecordBuffer.toString())){
      ParseWorkflowQueryRecordXML parseWorkflowQueryRecordXML = new ParseWorkflowQueryRecordXML();
      parseWorkflowQueryRecordXML.getParseing(form,request,workflowQueryRecord);

    }else{
      //do null
    }
    WorkflowGroupDAObject wfGroupDAO = new WorkflowGroupDAObject(sessionContainer, conn);
    
    if (!Utility.isEmpty(TextUtility.parseIntegerObj(workflowSearchForm.getWorkflowGroupID()) )) {
      WorkflowGroup wfGroup = (WorkflowGroup) wfGroupDAO.getObjectByID(TextUtility.parseIntegerObj(workflowSearchForm.getWorkflowGroupID()));
      request.setAttribute("workflowGroup", wfGroup);
    } else {
      request.setAttribute("workflowGroup", null);
    }
    
    super.getListData(mapping, form, request, response);
  }


  public void insertRecord(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws ApplicationException{
    try{
      ListWorkflowMonitoringForm maintForm = (ListWorkflowMonitoringForm)form;
      WorkflowQueryRecordDAObject workflowQueryRecordDAO = new WorkflowQueryRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
      WorkflowQueryRecord workflowQueryRecord=new WorkflowQueryRecord();
      
      if (WorkflowQueryRecord.QUERY_OP_TYPE_NEW.equals(maintForm.getOpType())) {
      	if (!this.checkDuplicateQueryName(maintForm.getQueryName(),request)){
      		this.GenQueryXML(workflowQueryRecord,form,request);
      		workflowQueryRecord.setUserRecordID(this.getSessionContainer(request).getUserRecordID());
      		workflowQueryRecord.setQueryName(maintForm.getQueryName());
      		workflowQueryRecord.setQueryDescription(maintForm.getQueryDescription());
          
          if (!Utility.isEmpty(maintForm.getWorkflowGroupID())) {
            workflowQueryRecord.setWorkflowGroupID(new Integer(maintForm.getWorkflowGroupID()));
          }
          
      		if (maintForm.getQueryType().equals(WorkflowQueryRecord.QUERY_TYPE_DEFAULT)){// if queryType is "D"
      			if(!this.checkDuplicateQueryType(this.getSessionContainer(request).getUserRecordID(),request)){
      				workflowQueryRecord.setQueryType(maintForm.getQueryType());
      			} else {
      				throw new ApplicationException(WorkflowErrorConstant.QUERY_TYPE_DUPLICATE);
      			}
      		} else {
      			workflowQueryRecord.setQueryType(maintForm.getQueryType());
      		}
      	} else {
      		throw new ApplicationException(WorkflowErrorConstant.QUERY_NAME_DUPLICATE);
      	}
      	workflowQueryRecord =(WorkflowQueryRecord)workflowQueryRecordDAO.insertObject(workflowQueryRecord);
    
      } else if (WorkflowQueryRecord.QUERY_OP_TYPE_EDIT.equals(maintForm.getOpType())) {
      	workflowQueryRecord = (WorkflowQueryRecord)workflowQueryRecordDAO.getObjectByID(new Integer(maintForm.getQueryID()));
      	this.GenQueryXML(workflowQueryRecord,form,request);
      	
        workflowQueryRecord.setUserRecordID(this.getSessionContainer(request).getUserRecordID());
      	workflowQueryRecord.setQueryName(maintForm.getQueryName());
      	workflowQueryRecord.setQueryDescription(maintForm.getQueryDescription());
      	workflowQueryRecord.setQueryType(maintForm.getQueryType());
        
        if (!Utility.isEmpty(maintForm.getWorkflowGroupID())) {
          workflowQueryRecord.setWorkflowGroupID(new Integer(maintForm.getWorkflowGroupID()));
        }
        
      	workflowQueryRecord = (WorkflowQueryRecord)workflowQueryRecordDAO.updateObject(workflowQueryRecord);
      }
      maintForm.setQueryID(workflowQueryRecord.getID().toString());
   }catch (ApplicationException appEx) {
     throw appEx;
   } catch (Exception e) {
     log.error(e, e);
     throw new ApplicationException(ErrorConstant.COMMON_FATAL_ERROR, e);
   } finally {}
 }

   /**
    * Generate Excel File
    * @param workflowProgressList
    * @param fileName
    * @param locale
    * @param request
    * @param response
    * @throws ApplicationException
    */
   private void generateExcelFile(
        List workflowProgressList,
        String fileName,
        Locale locale,
        HttpServletRequest request,
        HttpServletResponse response)
    throws ApplicationException {
      try{
      	UserRecordDAObject userRecordDAO = new UserRecordDAObject(this.getSessionContainer(request), this.getConnection(request));
      	WorkflowStepDAObject stepDAO = new WorkflowStepDAObject(this.getSessionContainer(request), this.getConnection(request));
      	MtmWorkflowProgressUserRecordDAObject wfProgressUserRecordDAO = new MtmWorkflowProgressUserRecordDAObject(this.getSessionContainer(request), this.getConnection(request));
        MtmWorkflowProgressSystemObjectDAObject sysObjectDAO = new MtmWorkflowProgressSystemObjectDAObject(this.getSessionContainer(request), this.getConnection(request));
        FormRecordDAObject formRecordDAO = new FormRecordDAObject(this.getSessionContainer(request), this.getConnection(request));
        
      	response.addHeader("Content-Transfer-Encoding", "base64");
      	response.addHeader("Content-Disposition", "attachment; filename=" + fileName + "\"");
      	
      	
      	//Set these Cell Format
      	WritableFont titleFont = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
      	WritableFont headerFont = new WritableFont(WritableFont.TIMES, 9, WritableFont.BOLD);
      	WritableFont bodyFont = new WritableFont(WritableFont.TIMES, 9, WritableFont.BOLD);
      	WritableFont footerFont = new WritableFont(WritableFont.TIMES, 9, WritableFont.BOLD);
      	
      	WritableCellFormat titleCellFormat = new WritableCellFormat(titleFont);
      	WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
      	WritableCellFormat bodyCellFormat = new WritableCellFormat(bodyFont);
      	WritableCellFormat footerCellFormat = new WritableCellFormat(footerFont);
      	WritableCellFormat remarkCellFormat = new WritableCellFormat(bodyFont);
      	WritableCellFormat seperatorCellFormatRight = new WritableCellFormat(bodyFont);
      	WritableCellFormat seperatorCellFormatLeft = new WritableCellFormat(bodyFont);
      	WritableCellFormat onLoanCellFormat = new WritableCellFormat(bodyFont);
      	
      	headerCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
      	bodyCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
      	//seperatorCellFormatRight.setBorder(jxl.format.Border.RIGHT,jxl.format.BorderLineStyle.THIN);
      	//seperatorCellFormatRight.setBorder(jxl.format.Border.BOTTOM,jxl.format.BorderLineStyle.THIN);
      	//seperatorCellFormatRight.setBorder(jxl.format.Border.TOP,jxl.format.BorderLineStyle.THIN);
      	seperatorCellFormatLeft.setBorder(jxl.format.Border.LEFT,jxl.format.BorderLineStyle.THIN);
      	seperatorCellFormatLeft.setBorder(jxl.format.Border.BOTTOM,jxl.format.BorderLineStyle.THIN);
      	seperatorCellFormatLeft.setBorder(jxl.format.Border.TOP,jxl.format.BorderLineStyle.THIN);
      	
      	headerCellFormat.setAlignment(Alignment.CENTRE);
      	bodyCellFormat.setAlignment(Alignment.CENTRE);
      	headerCellFormat.setVerticalAlignment(VerticalAlignment.TOP);
      	bodyCellFormat.setVerticalAlignment(VerticalAlignment.TOP);
      	headerCellFormat.setBackground(Colour.GRAY_25);
      	
      	seperatorCellFormatLeft.setBackground(Colour.GRAY_25);
      	//seperatorCellFormatRight.setBackground(Colour.GRAY_25);
      	onLoanCellFormat.setBackground(Colour.BLACK);
      	headerCellFormat.setWrap(true);
      	bodyCellFormat.setWrap(true);
      	
      	
      	ServletOutputStream sos = response.getOutputStream();
      	WritableWorkbook book = Workbook.createWorkbook(sos);
      	WritableSheet sheet = book.createSheet("Workflow Progress Monitoring",0);
      	
      	//          SheetSettings ss = ws.getSettings();
      	//          ss.setBottomMargin(0.5);
      	//          ss.setLeftMargin(0.5);
      	//          ss.setRightMargin(0.5);
      	//          ss.setTopMargin(0.5);
      	//          
      	//sheet.setFooter("","","page &P of &N");
      	sheet.setColumnView(0,10);//Track ID
      	sheet.setColumnView(1,10);//Progress Seq No.
      	sheet.setColumnView(2,10);//Priority
      	sheet.setColumnView(3,40);//Category
      	sheet.setColumnView(4,40);//Description
      	sheet.setColumnView(5,25);//Step Name
      	sheet.setColumnView(6,12);//Step Action Type
      	sheet.setColumnView(7,30);//Owner
      	sheet.setColumnView(8,12);//Submitter
      	sheet.setColumnView(9,16);//Status
      	sheet.setColumnView(10,20);//Workflow Code
      	sheet.setColumnView(11,10);//Competed
        sheet.setColumnView(12,20);//All Action Takers
        sheet.setColumnView(13,16);//Related Objects
        sheet.setColumnView(14,18);//Due Date
        sheet.setColumnView(15,18);//Task Arrived Date
        sheet.setColumnView(16,18);//Update Date
        sheet.setColumnView(17,15);//Updater
      	
      	Label label = null;
      	int row = 6;
      	
      	//set the title
      	label = new Label(4,1,MessageResourcesFactory.getMessage(locale, "workflow.label.workflow_progress_record_list") );//Workflow Progress Record List
      	label.setCellFormat(titleCellFormat);
      	sheet.addCell(label);
      	
      	//set the generation time
      	label = new Label(0,3,MessageResourcesFactory.getMessage(locale, "common.report_generation_time") );//Generation Time
      	label.setCellFormat(seperatorCellFormatRight);
      	sheet.addCell(label);
      	
      	label = new Label(2,3,TextUtility.formatTimestamp(Utility.getCurrentTimestamp()) );
      	//label.setCellFormat(bodyCellFormat);
      	sheet.addCell(label);
      	
      	//set the generated by
      	label = new Label(0,4,MessageResourcesFactory.getMessage(locale, "common.report_generated_by") );//Generation By
      	label.setCellFormat(seperatorCellFormatRight);
      	sheet.addCell(label);
      	
      	label = new Label(2,4,UserInfoFactory.getUserFullName(this.getSessionContainer(request).getUserRecordID()) );
      	//label.setCellFormat(bodyCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(0,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.track_id"));//Track ID
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
        label = new Label(1,row,MessageResourcesFactory.getMessage(locale, "workflow.label.progress_seq_no"));//Progress Seq No
        label.setCellFormat(headerCellFormat);
        sheet.addCell(label);
        
      	label = new Label(2,row,MessageResourcesFactory.getMessage(locale, "workflow.search.priority"));//Priority
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(3,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.category"));//CateGory
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(4,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.description"));//Description
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(5,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.step_name"));//Step Name
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);      	
        
        label = new Label(6,row,MessageResourcesFactory.getMessage(locale, "workflow.label.step_action_type"));//Step Action Type
        label.setCellFormat(headerCellFormat);
        sheet.addCell(label);
        
      	label = new Label(7,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.actor"));//Owner
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
        
        label = new Label(8,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.submitter"));//Submitter
        label.setCellFormat(headerCellFormat);
        sheet.addCell(label);
      	
      	label = new Label(9,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.status"));//Status
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(10,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.workflow_code"));//Workflow Code
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(11,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.complete_flag"));//Completed
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
        label = new Label(12,row,MessageResourcesFactory.getMessage(locale, "workflow.label.all_action_takers"));//All Action Takers
        label.setCellFormat(headerCellFormat);
        sheet.addCell(label);        
        
        label = new Label(13,row,MessageResourcesFactory.getMessage(locale, "workflow.label.related_objects"));//Related Objects
        label.setCellFormat(headerCellFormat);
        sheet.addCell(label);
        
      	label = new Label(14,row,MessageResourcesFactory.getMessage(locale, "workflow.columnheader.due_date"));//Due Date
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);      	

        label = new Label(15,row,MessageResourcesFactory.getMessage(locale, "workflow.label.task_arrived_date"));//Task Arrived Date
        label.setCellFormat(headerCellFormat);
        sheet.addCell(label);
                
      	label = new Label(16,row,MessageResourcesFactory.getMessage(locale, "common.columnheader.update_date"));//Update Date
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
      	
      	label = new Label(17,row,MessageResourcesFactory.getMessage(locale, "common.columnheader.updater"));//Updater
      	label.setCellFormat(headerCellFormat);
      	sheet.addCell(label);
              	
      	row++;
      	for(int i = 0 ;i<workflowProgressList.size() ;i++){
      		WorkflowProgress wfProgress = (WorkflowProgress)workflowProgressList.get(i);
      		//TrackID
      		label = new Label(0,row + i,wfProgress.getTrackID().toString());//TrackID
      		sheet.addCell(label);
            
      		//Progress Seq No.
      		label = new Label(1,row + i,wfProgress.getSeqNo().toString());//Progress Seq No.
      		sheet.addCell(label);
            
          //ProgressPriority  
      		if (GlobalConstant.PRIORITY_HIGH.equals(wfProgress.getProgressPriority())) {
      			label = new Label(2,row + i,MessageResourcesFactory.getMessage(locale, "workflow.label.priority_3"));//Priority
      		} else if (GlobalConstant.PRIORITY_MEDIUM.equals(wfProgress.getProgressPriority())) {
      			label = new Label(2,row + i,MessageResourcesFactory.getMessage(locale, "workflow.label.priority_2"));//Priority
      		} else if (GlobalConstant.PRIORITY_LOW.equals(wfProgress.getProgressPriority())) {
      			label = new Label(2,row + i,MessageResourcesFactory.getMessage(locale, "workflow.label.priority_1"));//Priority
      		}
      		sheet.addCell(label);
      		
          //Category
      		label = new Label(3,row + i,wfProgress.getWorkflowCategory());//Category
      		sheet.addCell(label);
      		
      		//Desecription
          label = new Label(4,row + i,wfProgress.getWorkflowDescription());//Description
      		sheet.addCell(label);
      		
      		//Step Name
      		label = new Label(5,row + i,wfProgress.getStepName());//Step Name
      		sheet.addCell(label);
      		
          // get the Step Action Type
          String actionType = "";          
          if(WorkflowStep.ACTION_TYPE_SUBMIT.equals(wfProgress.getActionType()) ){
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_S");//Submit Task
            
          } else if (WorkflowStep.ACTION_TYPE_APPROVAL.equals(wfProgress.getActionType())) {
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_A");//Approval Task
            
          } else if(WorkflowStep.ACTION_TYPE_TASK_TO_DO.equals(wfProgress.getActionType()) ){
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_T");//To Do Task
            
          } else if(WorkflowStep.ACTION_TYPE_BOOLEAN.equals(wfProgress.getActionType()) ){
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_B");//Yes/No Task
            
          } else if(WorkflowStep.ACTION_TYPE_END.equals(wfProgress.getActionType()) ){
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_E");//Workflow End Task
            
          } else if(WorkflowStep.ACTION_TYPE_FORM_DRIVEN.equals(wfProgress.getActionType()) ){
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_F");//Form Value Driven Task
            
          } else if(WorkflowStep.ACTION_TYPE_USER_DEFINED_TASK.equals(wfProgress.getActionType()) ){
            actionType = MessageResourcesFactory.getMessage(locale, "workflow.label.step_type_U");//Decision Point
          } 
           
          //Step Action Type
      		label = new Label(6,row + i,actionType);//Step Action Type
      		sheet.addCell(label);  
           
          //Owner  
      		List userActorList = (wfProgress).getUserActorList();
      		String owner = "";
      		for (int j=0; j<userActorList.size(); j++) {
      			MtmWorkflowStepUserActor userActor = (MtmWorkflowStepUserActor) userActorList.get(j);
      			String email = null;
      			String actorName = null;
      			if (MtmWorkflowStepUserActor.ACTOR_TYPE_USER.equals(userActor.getActorType())) {
      				actorName = UserInfoFactory.getUserFullName(userActor.getActorID());
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP.equals(userActor.getActorType())) {
      				actorName = userActor.getActorName();
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE.equals(userActor.getActorType())) {
      				actorName = userActor.getActorName();
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.everyone");        
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_REPORT_TO.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.report_to");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_SUBMITTER.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.submitter");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_RUNTIME_ASSIGN.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.dynamic_assign");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_RECURSIVE_RUNTIME_ASSIGN.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.recurrsive_dynamic_assign");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_PREVIOUS_TASK_OWNER.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.previous_task_owner");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_PREVIOUS_TASK_ACTION_TAKER.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.previous_task_action_taker");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_REPORT_TO_OF_PREVIOUS_TASK_ACTION_TAKER.equals(userActor.getActorType())) {
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.report_to_of_previous_task");
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_ACTION_TAKER.equals(userActor.getActorType())) {
      				WorkflowStep tmpStep = (WorkflowStep)stepDAO.getObjectByID( userActor.getActorID() );
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.step_n_action_taker", tmpStep.getStepSeq().toString());   
      			} else if (MtmWorkflowStepUserActor.ACTOR_TYPE_REPORT_TO_OF_ACTION_TAKER.equals(userActor.getActorType())) {
      				WorkflowStep tmpStep = (WorkflowStep)stepDAO.getObjectByID( userActor.getActorID() );  
      				actorName = MessageResourcesFactory.getMessage(locale, "workflow.label.step_n_action_taker_report_to", tmpStep.getStepSeq().toString());   
      			}
      			
      			if(j == 0){
      				owner = actorName;
      			} else {
      				owner += ";\n" + actorName;
      			}
      		}
      		label = new Label(7,row + i,owner);//Owner
      		sheet.addCell(label);
            
          //Submitter   
      		label = new Label(8,row + i,wfProgress.getSubmitter());//Submitter
      		sheet.addCell(label);
      		
          //Status  
          String status = "";
      		if (WorkflowProgress.STATUS_SUBMIT.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_S");// Submit Status
      		} else if (WorkflowProgress.STATUS_PENDING.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_P");// Pending Status
      		} else if (WorkflowProgress.STATUS_APPROVE.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_A");// Approve Status
      		} else if (WorkflowProgress.STATUS_REJECT.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_R");// Reject Status
      		} else if (WorkflowProgress.STATUS_YES.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_T");// Yes Status
      		} else if (WorkflowProgress.STATUS_NO.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_F");// No Status
      		} else if (WorkflowProgress.STATUS_NO_RESPONSE.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_N");// No Response Status
      		} else if (WorkflowProgress.STATUS_GENERAL_ACCOMPLISHMENT.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_D");// General Accomplishment Status
      		} else if (WorkflowProgress.STATUS_CANCEL.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_C");// Cancel Status
      			
      		} else if (WorkflowProgress.STATUS_IN_PROGRESS.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_I");// In Progress Status              
      		} else if (WorkflowProgress.STATUS_POST_PROGRESS.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_H");// Post Progress Status              
      		} else if (WorkflowProgress.STATUS_ROLLBACK.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_R");// Rollbalk Status              
      		} else if (WorkflowProgress.STATUS_REVOKE.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_V");// Revoke Status              
      		} else if (WorkflowProgress.STATUS_SKIP.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_K");// Skip Status              
      		} else if (WorkflowProgress.STATUS_LOCK.equals(wfProgress.getStatus())) {
      			status = MessageResourcesFactory.getMessage(locale, "workflow.label.step_status_L");// Lock Status              
      		} 
      		label = new Label(9,row + i,status);//Status
      		sheet.addCell(label);
      		
          //Workflow Code  
      		label = new Label(10,row + i,wfProgress.getWorkflowCode()+" - "+wfProgress.getWorkflowVersion());//Workflow Code
      		sheet.addCell(label);
      		
          //Completed  
      		if(GlobalConstant.TRUE.equals(wfProgress.getCompleteFlag()) ){
      			label = new Label(11,row + i,MessageResourcesFactory.getMessage(locale, "common.label.yes") );//Completed
      		} else {
      			label = new Label(11,row + i,MessageResourcesFactory.getMessage(locale, "common.label.no") );//Completed
      		}
      		sheet.addCell(label);
      		
          //get the all Action Takers.
          String actionTakers = "";
          List progressUserRecordList = wfProgressUserRecordDAO.getFinishedProgressListByWorkflowProgressID(wfProgress.getID());
      		for(int k = 0; k < progressUserRecordList.size(); k++){
            MtmWorkflowProgressUserRecord tmpUserRecord = (MtmWorkflowProgressUserRecord)progressUserRecordList.get(k);
            if (k > 0) {
              actionTakers += ",";  
            }
            actionTakers += UserInfoFactory.getUserFullName(tmpUserRecord.getUserRecordID());
          }
          label = new Label(12,row + i,actionTakers);//All Action Takers
      		sheet.addCell(label);            
      		
          //get Related Objects
          String relatedObject = "";
          MtmWorkflowProgressSystemObject sysObject = (MtmWorkflowProgressSystemObject)sysObjectDAO.getObjectByWorkflowProgressID(wfProgress.getID());
          
          if (!Utility.isEmpty(sysObject)) {
            JobDataMap dataMap = new JobDataMap( (Map)sysObject.getParameterObject() );
          	Object object = dataMap.get("WORKFLOW_OBJECT");
            
            if (object instanceof com.dcivision.form.bean.FormSubmission) {
          		com.dcivision.form.bean.FormSubmission formSubmission = (com.dcivision.form.bean.FormSubmission)object;
          		FormRecord formRecrod = (FormRecord)formRecordDAO.getByFormRecordID(formSubmission.getFormRecordID().toString());
              relatedObject = formRecrod.getFormCode() + "[" + formRecrod.getFormVersion() + "]";
              
          	} else if (object instanceof com.dcivision.workflow.bean.MtmWorkflowTrackInterruptReq) {
          		com.dcivision.workflow.bean.MtmWorkflowTrackInterruptReq interruptReq = (com.dcivision.workflow.bean.MtmWorkflowTrackInterruptReq)object;
          		//Rollback
              if (MtmWorkflowTrackInterruptReq.INTERRUPT_TYPE_ROLLBACK.equals(interruptReq.getInterruptType())) {
              //Revoke
              } else if (MtmWorkflowTrackInterruptReq.INTERRUPT_TYPE_REVOKE.equals(interruptReq.getInterruptType())) {
              //Skip 
              } else if (MtmWorkflowTrackInterruptReq.INTERRUPT_TYPE_SKIP.equals(interruptReq.getInterruptType())) {
              //Stop 
              } else if (MtmWorkflowTrackInterruptReq.INTERRUPT_TYPE_STOP.equals(interruptReq.getInterruptType())) {
             
              }
              
            } else if (object instanceof com.dcivision.dms.bean.DmsDocument){
              com.dcivision.dms.bean.DmsDocument dmsDoc = (com.dcivision.dms.bean.DmsDocument)object;
              relatedObject = dmsDoc.getDocumentName();
            }
          }
          
      		label = new Label(13,row + i,relatedObject);//Related Objects
      		sheet.addCell(label);      		
            
          //Due Date  
      		if(Utility.isEmpty(wfProgress.getDueDate())){
      			label = new Label(14,row + i,"");//Due Date
      		} else {
      			label = new Label(14,row + i,wfProgress.getDueDate().toString().substring(0,16));//Due Date
      		}
      		sheet.addCell(label);
      		
      		//Task Arrived Date
      		label = new Label(15,row + i,wfProgress.getCreateDate().toString().substring(0,16));//Task Arrived Date
      		sheet.addCell(label);
      		
      		//UPdate Date
      		label = new Label(16,row + i,wfProgress.getUpdateDate().toString().substring(0,16));//Update Date
      		sheet.addCell(label);
      		
      		//Updater
      		label = new Label(17,row + i,wfProgress.getUpdaterName());//Updater
      		sheet.addCell(label);          
                  		
      	}
      	book.write();
      	book.close();
      } catch (IOException e) {
      	throw new ApplicationException(e);
      } catch (WriteException we){
      	throw new ApplicationException(we);
      }

   }

   public void GenQueryXML(WorkflowQueryRecord workflowQueryRecord,ActionForm form,HttpServletRequest request) throws ApplicationException{
    //WriterBean writer=new WriterBean();
    try {
      SessionContainer ctx=this.getSessionContainer(request);
      Connection conn=this.getConnection(request);
      ListWorkflowMonitoringForm queryForm=(ListWorkflowMonitoringForm)form;

      //String[] rootFolder=new String[rootList.size()];

      WorkflowQueryRecordParser queryRecordParser = new WorkflowQueryRecordParser();
      queryRecordParser.setConnection(conn);
      queryRecordParser.setSessionContainer(ctx);

      queryRecordParser.setTaskType(queryForm.getTaskType());
      queryRecordParser.setPriority(queryForm.getPriority());
      queryRecordParser.setDatePeriodFrom(queryForm.getDatePeriodFrom());
      queryRecordParser.setDatePeriodTo(queryForm.getDatePeriodTo());
      queryRecordParser.setStatus(queryForm.getStatus());
      queryRecordParser.setWorkflowCategoryID(queryForm.getWorkflowCategoryID());
      queryRecordParser.setWorkflowRecordID(queryForm.getWorkflowRecordID());
      queryRecordParser.setActionTaker(queryForm.getActionTaker());
      queryRecordParser.setTrackID(queryForm.getTrackID());
      queryRecordParser.setStepName(queryForm.getStepName());
      queryRecordParser.setRelatedObject(queryForm.getRelatedObject());
      queryRecordParser.setWorkflowDescription(queryForm.getWorkflowDescription());
      queryRecordParser.setSubmitter(queryForm.getSubmitter());
      queryRecordParser.setPeriodTimes(queryForm.getPeriodTimes());
      queryRecordParser.setPeriodTimeType(queryForm.getPeriodTimeType());
      queryRecordParser.setPeriodType(queryForm.getPeriodType());
      queryRecordParser.setStageName(queryForm.getStageName());
      //added by nancy
      queryRecordParser.setTaskComment(queryForm.getTaskComment());
      queryRecordParser.setAttachmentName(queryForm.getAttachmentName());
      //end 
      queryRecordParser.setWorkflowGroupID(queryForm.getWorkflowGroupID());

      // Use a Transformer for output
      TransformerFactory tFactory = TransformerFactory.newInstance();
      Transformer transformer = tFactory.newTransformer();
      // Use the parser as a SAX source for input
      InputSource inputSource = new InputSource(); // no need to take input xml file
      SAXSource source = new SAXSource(queryRecordParser, inputSource);
      StringWriter writer=new StringWriter();
      StreamResult result = new StreamResult(writer);
      transformer.transform(source, result);
      //JobDataMap dataMap = new JobDataMap();
      //dataMap.put("XML_QUERY",writer);
      StringBuffer writerBuffer=new StringBuffer();
      writerBuffer.append(writer.toString());
      workflowQueryRecord.setXmlQuery(writerBuffer);
    } catch (Exception e) {
      log.error(e, e);
    }
  }
   
   /**
    * TODO get workflowQueryRecord is XMLQuery by userRecordID
    * @param userRecordID
    * @param request
    * @return
    * @throws ApplicationException
    */

   public WorkflowQueryRecord getQueryRecordByUserRecordID(Integer userRecordID,HttpServletRequest request) throws ApplicationException{
    StringBuffer queryRecordBuffer = new StringBuffer();
    WorkflowQueryRecord workflowQueryRecord = new WorkflowQueryRecord();
    try {
      SessionContainer ctx=this.getSessionContainer(request);
      Connection conn=this.getConnection(request);
      WorkflowQueryRecordDAObject workflowQueryRecordDAO = new WorkflowQueryRecordDAObject(ctx,conn);
      List workflowQueryRecordList = new ArrayList();
      String queryIDStr = request.getParameter("queryID");
      if (!Utility.isEmpty(queryIDStr)){
        workflowQueryRecord = (WorkflowQueryRecord)workflowQueryRecordDAO.getObjectByID(new Integer(queryIDStr));
      } else {
        if (Utility.isEmpty(request.getParameter("navMode")) && Utility.isEmpty(request.getParameter("opMode"))) {
          workflowQueryRecordList = workflowQueryRecordDAO.getListByUserRecordIDAndQueryType(userRecordID,WorkflowQueryRecord.QUERY_TYPE_DEFAULT);
          if (!Utility.isEmpty(workflowQueryRecordList)) {
            workflowQueryRecord = (WorkflowQueryRecord)workflowQueryRecordList.get(0);
          }
        }
      }
     } catch (ApplicationException ex){
       log.error(ex, ex);
     }
     return workflowQueryRecord;
   }
   
   /**
    * TODO check duplicate queryName
    * @param queryName
    * @param request
    * @return
    * @throws ApplicationException
    */
   public boolean checkDuplicateQueryName(String queryName,HttpServletRequest request) throws ApplicationException {

    List workflowQueryRecordList = null;
    try {
      WorkflowQueryRecordDAObject workflowQueryRecordDAO = new WorkflowQueryRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
      workflowQueryRecordList = workflowQueryRecordDAO.getListByQueryName(queryName);
    } catch (ApplicationException ex) {
      log.error(ex, ex);
    }
    if (!(Utility.isEmpty(workflowQueryRecordList)) && workflowQueryRecordList.size() > 0){
      return true;
    }
   return false;
   }

    /**
     * TODO check duplicate where queryType value = "D"
     * @param queryName
     * @param request
     * @return
     * @throws ApplicationException
     */
   public boolean checkDuplicateQueryType(Integer userRecordID,HttpServletRequest request) throws ApplicationException {
    List workflowQueryRecordList = new ArrayList();
    try {
     WorkflowQueryRecordDAObject workflowQueryRecordDAO = new WorkflowQueryRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
    workflowQueryRecordList = workflowQueryRecordDAO.getListByUserRecordIDAndQueryType(userRecordID,WorkflowQueryRecord.QUERY_TYPE_DEFAULT);
    } catch (ApplicationException ex) {
      log.error(ex, ex);
    }
    if (!Utility.isEmpty(workflowQueryRecordList) && workflowQueryRecordList.size()>0){
      return true;
    }
    return false;
   }
   
   /**
    * get workflowGroup string array 
    * @param request
    * @return String[][]
    * @throws ApplicationException
    */
   private String[][] getWorkflowGroup(HttpServletRequest request) throws ApplicationException {
     String[][] workflowGroupArr = null;
     WorkflowGroupDAObject workflowGroupDAO = new WorkflowGroupDAObject(this.getSessionContainer(request), this.getConnection(request));
     List workflowGroupList = workflowGroupDAO.getAccessibleMonitoringList();
     workflowGroupArr = new String[workflowGroupList.size() + 1][2];
     workflowGroupArr[0][0] = MessageResourcesFactory.getMessage(this.getLocale(request), "common.options.select_menu.select_one");
     workflowGroupArr[0][1] = "";
     
     for (int i = 0; i < workflowGroupList.size(); i++) {
       WorkflowGroup workflowGroup = (WorkflowGroup)workflowGroupList.get(i);
       workflowGroupArr[i + 1][0] = workflowGroup.getGroupName();
       workflowGroupArr[i + 1][1] = workflowGroup.getID().toString();
     }
     
     return workflowGroupArr;
   }
}
